package com.sky.service.impl;

import cn.hutool.db.sql.Order;
import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrdersMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.vo.*;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.ZoneId;
import java.time.temporal.ChronoUnit;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.stream.Collectors;

@Service
public class ReportServiceImpl implements ReportService {
    @Autowired
    private OrdersMapper ordersMapper;

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private WorkSpaceServiceImpl workSpaceService;

    @Override
    public TurnoverReportVO getTurnover(LocalDate begin, LocalDate end) {
        //1.创建存储开始时间到结束时间的集合
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);
        while (!begin.equals(end)) {
            begin = begin.plusDays(1);//每次+1天
            dateList.add(begin);
        }
        //2.创建存储营业额的集合
        List<Double> turnoverList = new ArrayList<>();
        for (LocalDate date : dateList) {
            //获取该日期对应的最早时间 比如2023-01-01 00:00:00
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            //获取最晚时间 比如2023-12-31 23:59:59
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            //将LocalDateTime转换为Date类型
            Date beginTimeDate = Date.from(beginTime.atZone(java.time.ZoneId.systemDefault()).toInstant());
            Date endTimeDate = Date.from(endTime.atZone(java.time.ZoneId.systemDefault()).toInstant());

            HashMap map = new HashMap();
            map.put("status", Orders.COMPLETED);//已完成订单
            map.put("begin", beginTimeDate);
            map.put("end", endTimeDate);
            //查询该日期对应的营业额
            Double turnover = ordersMapper.sumByMap(map);
            turnover = turnover == null ? 0.0 : turnover;
            turnoverList.add(turnover);
        }
        //3.封装数据
        TurnoverReportVO turnoverReportVO = new TurnoverReportVO();
        turnoverReportVO.setDateList(StringUtils.join(dateList, ","));
        turnoverReportVO.setTurnoverList(StringUtils.join(turnoverList, ","));

        return turnoverReportVO;
    }

    @Override
    public UserReportVO getUserReport(LocalDate begin, LocalDate end) {
        //1.创建存储开始时间到结束时间的集合
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);
        while (!begin.equals(end)) {
            begin = begin.plusDays(1);//每次+1天
            dateList.add(begin);
        }
        //2.创建存储新增用户数的集合
        List<Integer> newUserList = new ArrayList<>();
        //3.创建存储总用户数的集合
        List<Integer> totalUserList = new ArrayList<>();

        for (LocalDate date : dateList) {
            //获取该日期对应的最早时间 比如2023-01-01 00:00:00
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            //获取最晚时间 比如2023-12-31 23:59:59
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            //将LocalDateTime转换为Date类型
            Date beginTimeDate = Date.from(beginTime.atZone(java.time.ZoneId.systemDefault()).toInstant());
            Date endTimeDate = Date.from(endTime.atZone(java.time.ZoneId.systemDefault()).toInstant());
            //查询该日期对应的总用户数
            Integer totalUser = getUserCount(null, endTimeDate);
            totalUserList.add(totalUser == null ? 0 : totalUser);
            //查询该日期对应的新增用户数
            Integer newUser = getUserCount(beginTimeDate, endTimeDate);
            newUserList.add(newUser == null ? 0 : newUser);
        }
        UserReportVO userReportVO = new UserReportVO();
        userReportVO.setDateList(StringUtils.join(dateList, ","));
        userReportVO.setTotalUserList(StringUtils.join(totalUserList, ","));
        userReportVO.setNewUserList(StringUtils.join(newUserList, ","));
        return userReportVO;
    }

    @Override
    public OrderReportVO getOrderReport(LocalDate begin, LocalDate end) {
        //1.创建存储开始时间到结束时间的集合
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(begin);
        while (!begin.equals(end)) {
            begin = begin.plusDays(1);//每次+1天
            dateList.add(begin);
        }
        //2.创建存储每天的订单数的集合
        List<Integer> orderCountList = new ArrayList<>();
        //3.创建存储每天的有效订单数的集合
        List<Integer> validOrderCountList = new ArrayList<>();
        for (LocalDate date : dateList) {
            //获取该日期对应的最早时间 比如2023-01-01 00:00:00
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            //获取最晚时间 比如2023-12-31 23:59:59
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);

            //将LocalDateTime转换为Date类型
            Date beginTimeDate = Date.from(beginTime.atZone(java.time.ZoneId.systemDefault()).toInstant());
            Date endTimeDate = Date.from(endTime.atZone(java.time.ZoneId.systemDefault()).toInstant());

            //查询每天的订单总数
            Integer orderCount = getOrderCount(beginTimeDate, endTimeDate, null);

            //查询每天的有效订单数
            Integer validOrdeCount = getOrderCount(beginTimeDate, endTimeDate, Orders.COMPLETED);
            orderCountList.add(orderCount);
            validOrderCountList.add(validOrdeCount);
        }
        //时间区间内的总订单数（也就是指定区间内的所有天数之和的订单数）
        Integer totalOrderCount = orderCountList.stream().reduce(Integer::sum).get();
        //时间区间内的有效订单数（也就是指定区间内的所有天数之和的有效订单数）
        Integer validOrdeCount = validOrderCountList.stream().reduce(Integer::sum).get();
        //订单完成率(有效订单数/总订单数）
        Double orderCompletionRate = validOrdeCount * 1.0 / totalOrderCount;
        return OrderReportVO.builder().dateList(StringUtils.join(dateList, ",")).orderCountList(StringUtils.join(orderCountList, ",")).validOrderCountList(StringUtils.join(validOrderCountList, ",")).totalOrderCount(totalOrderCount).validOrderCount(validOrdeCount).orderCompletionRate(orderCompletionRate).build();
    }

    @Override
    public SalesTop10ReportVO getSalesRanking(LocalDate begin, LocalDate end) {
        //获取该日期对应的最早时间 比如2023-01-01 00:00:00
        LocalDateTime beginTime = LocalDateTime.of(begin, LocalTime.MIN);
        //获取最晚时间 比如2023-12-31 23:59:59
        LocalDateTime endTime = LocalDateTime.of(end, LocalTime.MAX);

        //将LocalDateTime转换为Date类型
        Date beginTimeDate = Date.from(beginTime.atZone(java.time.ZoneId.systemDefault()).toInstant());
        Date endTimeDate = Date.from(endTime.atZone(java.time.ZoneId.systemDefault()).toInstant());
        //查询销量排行
        List<GoodsSalesDTO> goodsSalesDTOList = ordersMapper.getSalesRanking(beginTimeDate, endTimeDate);

        //封装数据
        //获取销量排行对应的商品名称集合
        String nameList = StringUtils.join(goodsSalesDTOList.stream().map(GoodsSalesDTO::getName).collect(Collectors.toList()), ",");

        //获取销量排行对应的商品数量集合
        String numberList = StringUtils.join(goodsSalesDTOList.stream().map(GoodsSalesDTO::getNumber).collect(Collectors.toList()), ",");

        return SalesTop10ReportVO.builder().nameList(nameList).numberList(numberList).build();


    }

    /**
     * 导出营业额数据报表
     *
     * @param response
     */
    @Override
    public void exportBusinessData(HttpServletResponse response) throws IOException {
        LocalDate begin2 = LocalDate.now().minusDays(30);//2023-01-01
        LocalDate end2 = LocalDate.now().minusDays(1);//2023-01-31
        //1.查询数据库，获取营业额数据
        //默认查询最近30天的数据
        //获取最近30天对应的最早时间 比如2023-01-01 00:00:00
        //获取最晚时间 比如2023-01-31 23:59:59
        Date begin = Date.from(begin2.atStartOfDay().atZone(java.time.ZoneId.systemDefault()).toInstant());
        Date end = Date.from(end2.atStartOfDay().atZone(java.time.ZoneId.systemDefault()).toInstant());

        //把Date格式转换成2023-01-01 00:00:00 格式的字符串

        BusinessDataVO businessDataVO = workSpaceService.getBusinessData(begin, end);

        //读取Excel模板文件
        InputStream inputStream = this.getClass().getClassLoader().getResourceAsStream("运营数据报表模板.xlsx");


        //2.通过POI将数据写入到Excel表中
        //基于提供好的的运营数据报表模板.xlsx创建一个Workbook对象
        XSSFWorkbook excel = new XSSFWorkbook(inputStream);
        //获得Excel文件中的第一个Sheet
        XSSFSheet sheet = excel.getSheet("Sheet1");
        //向这个表设置日期区间 比如2023-01-01至2023-01-31
        sheet.getRow(1).getCell(1).setCellValue(begin2.toString() + "至" + end2.toString());
        //获取第四行填充营业额等数据
        XSSFRow row = sheet.getRow(3);//第四行
        row.getCell(2).setCellValue(businessDataVO.getTurnover());//营业额
        row.getCell(4).setCellValue(businessDataVO.getOrderCompletionRate());//订单完成率
        row.getCell(6).setCellValue(businessDataVO.getNewUsers());//新增用户数
        row = sheet.getRow(4);//第5行
        row.getCell(2).setCellValue(businessDataVO.getValidOrderCount());//有效订单数
        row.getCell(4).setCellValue(businessDataVO.getUnitPrice());//客单价

        LocalDate beginDate = LocalDate.now().minusDays(30);//今天往前30天
        LocalDate endDate = beginDate.minusDays(1);//今天往前1天

        //填充明细数据
        for (int i = 0; i < 30; i++) {
            LocalDate date = beginDate.plusDays(i);//往后+i天

            //获取该日期对应的最早时间 比如2023-01-01 00:00:00
            LocalDateTime beginTime = LocalDateTime.of(date, LocalTime.MIN);
            //获取最晚时间 比如2023-12-31 23:59:59
            LocalDateTime endTime = LocalDateTime.of(date, LocalTime.MAX);
            //将LocalDateTime转换为Date类型
            Date beginTimeDate = Date.from(beginTime.atZone(java.time.ZoneId.systemDefault()).toInstant());
            Date endTimeDate = Date.from(endTime.atZone(java.time.ZoneId.systemDefault()).toInstant());

            //查询每天的营业数据
            BusinessDataVO businessData = workSpaceService.getBusinessData(beginTimeDate, endTimeDate);
            //填充明细数据
            row = sheet.getRow(i + 7);//从第8行开始填充
            row.getCell(1).setCellValue(date.toString());//第二个单元格填充日期
            row.getCell(2).setCellValue(businessData.getTurnover());//第3个单元格填充营业额
            row.getCell(3).setCellValue(businessData.getValidOrderCount());//第4个单元格填充有效订单
            row.getCell(4).setCellValue(businessData.getOrderCompletionRate());//第5个单元格填充订单完成率
            row.getCell(5).setCellValue(businessData.getUnitPrice());//第6个单元格填充平均客单价
            row.getCell(6).setCellValue(businessData.getNewUsers());//第7个单元格填充新增用户数


        }


        //3.通过输出流将Excel文件下载到客户端浏览器
        ServletOutputStream outputStream = response.getOutputStream();
        excel.write(outputStream);
        outputStream.flush();
        outputStream.close();
        excel.close();
    }

    private Integer getOrderCount(Date beginTimeDate, Date endTimeDate, Integer status) {
        HashMap map = new HashMap();
        map.put("begin", beginTimeDate);
        map.put("end", endTimeDate);
        map.put("status", status);
        return ordersMapper.getOrderCount(map);
    }

    private Integer getUserCount(Date beginTimeDate, Date endTimeDate) {
        HashMap map = new HashMap();
        map.put("begin", beginTimeDate);
        map.put("end", endTimeDate);

        return userMapper.getUserCount(map);
    }
}
